################
# 1 Einleitung #
################

library(ggplot2)
library(ggmap)
library(RSQLite)
library(reshape)

drv <- dbDriver("SQLite")
con <- dbConnect(drv, "../data/CongoDB.sqlite")

# bounding-box Arbeitsgebiet
df_sitesDS = dbGetQuery(con, "SELECT t_Ort.ort_name, t_Ort.x_long, t_Ort.y_lat FROM t_Ort WHERE (((t_Ort.y_lat) <> '') AND ((t_Ort.ort_lit) Like '%DS%'))")
xmin <- min(df_sitesDS$x_long)
xmax <- max(df_sitesDS$x_long)
ymin <- min(df_sitesDS$y_lat)
ymax <- max(df_sitesDS$y_lat)

# Karte vom Arbeitsgebeit / Überischt
qmap("lac tele", zoom = 3, color = 'bw') + 
  geom_rect(mapping = aes(xmin = xmin, xmax = xmax, ymin = ymin, ymax = ymax, fill = TRUE), alpha = 0.1, color = 'black', fill = 'black') + 
  theme(legend.position = 'none')
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=lac+tele&zoom=3&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=lac%20tele&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead

#ggsave('../output/figs/1_Einleitung_Arbeitsgebiet1.pdf', width = 11, height = 11)

# *sonst.:*
qmap("lac tele", zoom = 3, maptype = "satellite") + 
  geom_rect(mapping = aes(xmin = xmin, xmax = xmax, ymin = ymin, ymax = ymax, fill = TRUE), alpha = 0, color="red") + 
  theme(legend.position = "none", 
        axis.ticks = element_blank(), 
        axis.text.x = element_blank(), 
        axis.text.y = element_blank(), 
        axis.title.x = element_blank(),
        axis.title.y = element_blank())
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=lac+tele&zoom=3&size=640x640&scale=2&maptype=satellite&language=en-EN&sensor=false
## Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=lac%20tele&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead

ggsave("../output/figs/1_Einleitung_Arbeitsgebiet2.pdf", width = 11, height = 11)

qmap("lac tele", zoom = 3) + geom_rect(mapping=aes(xmin=xmin, xmax=xmax, ymin=ymin, ymax=ymax, fill=TRUE), alpha = 0, color="red") + theme(legend.position="none")
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=lac+tele&zoom=3&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=lac%20tele&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead

# Kartierung aller Fundstellen
# >> Datenaufbereitung in Python
# 
# Daten einlesen:
df <- read.csv("../data/processed/1 Fundstellen - Kartierung.csv")

# Kartierung
qmap(c(10, -5, 25, 7.5), zoom = 6, color = 'bw', legend = "topleft") + 
  geom_point(aes(x = x_long, y = y_lat, shape = ort_lit), data = df, size=3, fill = "white") + 
  scale_shape_manual(values=c(25,21, 24))
## Warning: bounding box given to google - spatial extent only approximate.
## converting bounding box to center/zoom specification. (experimental)
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=1.25,17.5&zoom=6&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead
## Warning: Removed 105 rows containing missing values (geom_point).

#ggsave("../output/figs/1_Einleitung_Fundstellen1.pdf", width = 11, height = 11)

qmap(c(10, -5, 25, 7.5), zoom = 6) + 
  geom_rect(mapping = aes(xmin = xmin, xmax = xmax, ymin = ymin, ymax = ymax), alpha = 0, color="red", size = 0.2) + 
  geom_point(aes(x = x_long, y = y_lat, fill = ort_lit), data = df, size=3, shape = 21) + 
  theme(legend.justification=c(1,0), 
      legend.position=c(1,0), 
      axis.ticks = element_blank(), 
      axis.text.x = element_blank(), 
      axis.text.y = element_blank(), 
      axis.title.x = element_blank(),
      axis.title.y = element_blank()) + 
  scale_fill_discrete(guide_legend(title = "Quelle"))
## Warning: bounding box given to google - spatial extent only approximate.
## converting bounding box to center/zoom specification. (experimental)
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=1.25,17.5&zoom=6&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead

## Warning: Removed 105 rows containing missing values (geom_point).

ggsave("../output/figs/1_Einleitung_Fundstellen2.pdf", width = 11, height = 11)
## Warning: Removed 105 rows containing missing values (geom_point).
# Kartierung der eigene FdSt im Arbeitsgebiet
df_sitesDS = dbGetQuery(con, "SELECT
           t_Ort.ort_name,
           t_Ort.'Kat-Nr',
           t_Ort.x_long,
           t_Ort.y_lat
       FROM t_Ort
       WHERE (((t_Ort.y_lat) <> '')
           AND ((t_Ort.ort_lit) Like '%DS%'))")
colnames(df_sitesDS)[2] <- "Kat.Nr"
head(df_sitesDS)
##   ort_name Kat.Nr   x_long     y_lat
## 1    Gombe    237 17.58041 -0.704069
## 2  Bojenjo    292 17.43206 -0.006791
## 3   Bokuma    295 17.47815  0.099491
## 4   Boleko    285 17.22193 -0.805556
## 5  Botwale    286 17.36548 -0.552494
## 6  Bwanela    298 17.28831  0.505637
df_sitesNotDS = dbGetQuery(con, "SELECT
           t_Ort.ort_name,
           t_Ort.'Kat-Nr',
           t_Ort.x_long,
           t_Ort.y_lat
       FROM t_Ort
       WHERE (((t_Ort.y_lat) <> '')
           AND ((t_Ort.ort_lit) Not Like '%DS%'))")
colnames(df_sitesNotDS)[2] <- "Kat.Nr"

# df <- read.csv("~/Dropbox/Dissertation/Auswertung/data/processed/1 Fundstellen DS - Kartierung.csv")

qmap("Dongou", zoom = 7, color = 'bw') + 
  geom_point(aes(x = x_long, y = y_lat), data = df, size=3, shape = 21, fill = "white") + 
  scale_shape_manual(values=c(21,23, 24)) + 
  theme(legend.position="none") + 
  geom_text(aes(x = x_long+0.05, y = y_lat+0.05, label = ort_name), data = df, size = 2, hjust=0, vjust=0)
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=Dongou&zoom=7&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=Dongou&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead
## Warning: Removed 341 rows containing missing values (geom_point).
## Warning: Removed 343 rows containing missing values (geom_text).

#ggsave("../output/figs/1_Einleitung_FundstellenName.pdf", width = 11, height = 11)

qmap("Dongou", zoom = 7, color = 'bw') + 
  geom_point(aes(x = x_long, y = y_lat), data = df_sitesNotDS, size = 2, shape = 21, fill = "white") + 
  geom_point(aes(x = x_long, y = y_lat), data = df_sitesDS, size = 2, shape = 19) + 
  theme(legend.position="none") + 
  geom_text(aes(x = x_long+0.03, y = y_lat, label = Kat.Nr), data = df_sitesDS, size = 3, hjust=0, vjust=0)
## Map from URL : http://maps.googleapis.com/maps/api/staticmap?center=Dongou&zoom=7&size=640x640&scale=2&maptype=terrain&language=en-EN&sensor=false
## Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=Dongou&sensor=false
## Warning: `panel.margin` is deprecated. Please use `panel.spacing` property
## instead
## Warning: Removed 348 rows containing missing values (geom_point).
## Warning: Removed 2 rows containing missing values (geom_text).

#ggsave("../output/figs/1_Einleitung_FundstellenKatNr.pdf", width = 11, height = 11)